﻿using Mysoft.Map.Extensions.DAL;
using Mysoft.QuickCode.DbExpressionExtensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;

namespace Mysoft.QuickCode.DAL
{
    /// <summary>
    /// 表信息
    /// </summary>
    class TableInfo
    {
        public string TableName { set; get; }
        public string PrimaryKeyName { set; get; }
    }

    /// <summary>
    /// 不明确数据类型数据库访问对象。内置基本的CRUD方法。
    /// </summary>
    public sealed class BaseDAL
    {
        /// <summary>
        /// 获取表信息：表名，主键
        /// </summary>
        /// <returns></returns>
        TableInfo GetTableInfo<T>(T entity)
        {
            Type type = entity.GetType();
            //表名
            DataEntityAttribute entityAttr = type.GetCustomAttributes(false).FirstOrDefault(m => m is DataEntityAttribute) as DataEntityAttribute;
            string tbName = entityAttr == null ? type.Name : entityAttr.Alias;    //没有定义别名的情况下，用类名做表名

            //主键名
            //PropertyInfo[] ps = type.GetProperties();
            //DataColumnAttribute a = ps[0].GetCustomAttributes(false).FirstOrDefault(c => c is DataColumnAttribute) as DataColumnAttribute;
            PropertyInfo propPrimaryKey = type.GetProperties()
                .FirstOrDefault(
                    m => ((m.GetCustomAttributes(false).FirstOrDefault(c => c is DataColumnAttribute) as DataColumnAttribute).PrimaryKey == true)
                ) as PropertyInfo;
            if (propPrimaryKey == null)
            {
                throw new Exception(string.Format("对象{0}未找到属性 PrimaryKey=true 的主键！", type.Name));
            }

            return new TableInfo
            {
                TableName = tbName,
                PrimaryKeyName = propPrimaryKey.Name
            };
        }


        /// <summary>
        /// 新增或更新：如果主键ID值在数据库中已经存在则会执行更新，否则执行新增。
        /// </summary>
        /// <param name="entity">要保存的对象信息</param>
        /// <returns>主键ID</returns>
        public object SaveEntity<T>(T entity)
        {
            TableInfo ti = this.GetTableInfo(entity);
            string sql = "SELECT TOP 1 1 FROM {0} WHERE {1}=@ID";
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            PropertyInfo pi = entity.GetType().GetProperty(ti.PrimaryKeyName);
            object id = pi.GetValue(entity, null);    //获取主键值
            if (CPQuery.From(sql, new { ID = id }).ExecuteScalar<int>() == 1)
            {
                //如果存在此记录，则执行更新
                (entity as BaseEntity).Update();
            }
            else
            {
                //如此不存在此记录，则新增

                //如果主键是GUID，且没有赋值，则帮GUID赋默认值
                if (pi.PropertyType == typeof(Guid) && Guid.Empty.Equals(id))
                {
                    pi.SetValue(entity, Guid.NewGuid(), null);
                }
                (entity as BaseEntity).Insert();
            }

            return pi.GetValue(entity, null);
        }

        /// <summary>
        /// 删除实体对应的表数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">实体主键</param>
        /// <param name="objectInstance">实体对象实例。会通过反射获取对应的表映射信息。</param>
        public void DeleteEntity<T>(object id, T objectInstance)
        {
            string sql = "DELETE FROM {0} WHERE {1}=@ID";
            TableInfo ti = this.GetTableInfo(objectInstance);
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            CPQuery.From(sql, new { ID = id }).ExecuteNonQuery();
        }
    }

    /// <summary>
    /// 泛型数据类型数据库访问层。内置多种基础数据库访问方法，代替SQL编码。
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public sealed class BaseDAL<T>
        where T : BaseEntity, new()
    {
        /// <summary>
        /// 获取表信息：表名，主键
        /// </summary>
        /// <returns></returns>
        TableInfo GetTableInfo()
        {
            Type type = typeof(T);
            //表名
            DataEntityAttribute entityAttr = type.GetCustomAttributes(false).FirstOrDefault(m => m is DataEntityAttribute) as DataEntityAttribute;
            string tbName = entityAttr == null ? type.Name : entityAttr.Alias;    //没有定义别名的情况下，用类名做表名

            //主键名
            //PropertyInfo[] ps = type.GetProperties();
            //DataColumnAttribute a = ps[0].GetCustomAttributes(false).FirstOrDefault(c => c is DataColumnAttribute) as DataColumnAttribute;
            PropertyInfo propPrimaryKey = type.GetProperties()
                .FirstOrDefault(
                    m => ((m.GetCustomAttributes(false).FirstOrDefault(c => c is DataColumnAttribute) as DataColumnAttribute).PrimaryKey == true)
                ) as PropertyInfo;
            if (propPrimaryKey == null)
            {
                throw new Exception(string.Format("对象{0}未找到属性 PrimaryKey=true 的主键！", type.Name));
            }

            return new TableInfo
            {
                TableName = tbName,
                PrimaryKeyName = propPrimaryKey.Name
            };
        }

        /// <summary>
        /// 新增或更新：如果主键ID值在数据库中已经存在则会执行更新，否则执行新增。
        /// </summary>
        /// <param name="entity">要保存的对象信息</param>
        /// <returns></returns>
        public T SaveEntity(T entity)
        {
            TableInfo ti = this.GetTableInfo();
            string sql = "SELECT TOP 1 1 FROM {0} WHERE {1}=@ID";
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            PropertyInfo pi = typeof(T).GetProperty(ti.PrimaryKeyName);
            object id = pi.GetValue(entity, null);    //获取主键值
            if (CPQuery.From(sql, new { ID = id }).ExecuteScalar<int>() == 1)
            {
                //如果存在此记录，则执行更新
                (entity as BaseEntity).Update();
            }
            else
            {
                //如此不存在此记录，则新增

                //如果主键是GUID，且没有赋值，则帮GUID赋默认值
                if (pi.PropertyType == typeof(Guid) && Guid.Empty.Equals(id))
                {
                    pi.SetValue(entity, Guid.NewGuid(), null);
                }
                (entity as BaseEntity).Insert();
            }

            return entity;
        }

        /// <summary>
        /// 删除数据库中主键对应的记录
        /// </summary>
        /// <param name="id">数据库中的主键</param>
        /// <returns></returns>
        public void DeleteEntity(object id)
        {
            string sql = "DELETE FROM {0} WHERE {1}=@ID";
            TableInfo ti = this.GetTableInfo();
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            CPQuery.From(sql, new { ID = id }).ExecuteNonQuery();
        }

        /// <summary>
        /// 从数据库中获取数据并转换为对应的实体
        /// </summary>
        /// <param name="id">数据库中的主键</param>
        /// <returns></returns>
        public T GetEntity(object id)
        {
            string sql = "SELECT TOP 1 * FROM {0} WHERE {1}=@ID";
            TableInfo ti = this.GetTableInfo();
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            return CPQuery.From(sql, new { ID = id }).ToSingle<T>();
        }

        /// <summary>
        /// 查询ID对应的数据，并填充到DataTable
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetEntityTable(object id)
        {
            string sql = "SELECT TOP 1 * FROM {0} WHERE {1}=@ID";
            TableInfo ti = this.GetTableInfo();
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            return CPQuery.From(sql, new { ID = id }).FillDataTable();
        }

        /// <summary>
        /// 判断主键对应的记录是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Exists(object id)
        {
            string sql = "SELECT TOP 1 1 FROM {0} WHERE {1}=@ID";
            TableInfo ti = this.GetTableInfo();
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            return CPQuery.From(sql, new { ID = id }).ExecuteScalar<int>() == 1;
        }

        /// <summary>
        /// 高性能批量插入数据
        /// </summary>
        /// <param name="entityList">批量插入的数据集合</param>
        public void BatchInsert(List<T> entityList)
        {
            new EntityTable<T>().BatchInsert(entityList);
        }

        #region Linq方式的CRUD实现的方法

        /// <summary>
        /// Expression方式查询对象
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expr">查询条件Expression</param>
        /// <returns></returns>
        public T GetEntity(Expression<Func<T, bool>> expr)
        {
            return EntityTable<T>.Instance
                .Select()
                .Top(1)
                .Where(expr)
                .AsQuery().ToSingle<T>();
        }

        public T GetEntity(Expression<Func<T, bool>> expr, Expression<Func<T, object[]>> orderFields, OrderByType order)
        {
            return EntityTable<T>.Instance
                .Select()
                .Top(1)
                .Where(expr)
                .OrderBy(orderFields, order)
                .AsQuery().ToSingle<T>();
        }

        /// <summary>
        /// 条件查询实体列表
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expr">查询条件Expression</param>
        /// <returns></returns>
        public List<T> GetEntityList(Expression<Func<T, bool>> expr)
        {
            return EntityTable<T>.Instance
                .Select()
                .Where(expr)
                .AsQuery().ToList<T>();
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <returns></returns>
        public List<T> GetEntityList()
        {
            return EntityTable<T>.Instance
                .Select()
                .AsQuery().ToList<T>();
        }

        /// <summary>
        /// 查询实体集合，并排序
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expr">查询条件Expression</param>
        /// <param name="orderFields">排序字段</param>
        /// <param name="order">排序方式</param>
        /// <returns></returns>
        public List<T> GetEntityList(Expression<Func<T, bool>> expr, Expression<Func<T, object[]>> orderFields, OrderByType order)
        {
            return EntityTable<T>.Instance
                .Select()
                .Where(expr)
                .OrderBy(orderFields, order)
                .AsQuery().ToList<T>();
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <param name="orderFields"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<T> GetEntityList(Expression<Func<T, object[]>> orderFields, OrderByType order)
        {
            return EntityTable<T>.Instance
                .Select()
                .OrderBy(orderFields, order)
                .AsQuery().ToList<T>();
        }


        /// <summary>
        /// 按条件查询数据，并填充到DataTable
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expr">查询条件Expression</param>
        /// <returns></returns>
        public DataTable GetEntityTable(Expression<Func<T, bool>> expr)
        {
            return EntityTable<T>.Instance
                .Select()
                .Where(expr)
                .AsQuery().FillDataTable();
        }

        /// <summary>
        /// 查询所有数据到DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable GetEntityTable()
        {
            return EntityTable<T>.Instance
                .Select()
                .AsQuery().FillDataTable();
        }
        public DataTable GetEntityTable(Expression<Func<T, bool>> expr, Expression<Func<T, object[]>> orderFields, OrderByType order)
        {
            return EntityTable<T>.Instance
                .Select()
                .Where(expr)
                .OrderBy(orderFields, order)
                .AsQuery().FillDataTable();
        }

        /// <summary>
        /// Expression方式更新
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expUpdate">更新字段</param>
        /// <param name="expWhere">更新条件</param>
        /// <returns></returns>
        public void UpdateEntity(Expression<Func<T, T>> expUpdate, Expression<Func<T, bool>> expWhere)
        {
            EntityTable<T>.Instance
                .Update(expUpdate)
                .Where(expWhere)
                .AsQuery().ExecuteNonQuery();
        }

        /// <summary>
        /// Expression方式批量删除对象
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expWhere">删除条件</param>
        public void DeleteEntities(Expression<Func<T, bool>> expWhere)
        {
            EntityTable<T>.Instance
                .Delete()
                .Where(expWhere)
                .AsQuery().ExecuteNonQuery();
        }

        /// <summary>
        /// 判断主键对应的记录是否存在
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expWhere">查询条件</param>
        /// <returns></returns>
        public bool Exists(Expression<Func<T, bool>> expWhere)
        {
            return EntityTable<T>.Instance
                .Select()
                .Top(1)
                .Where(expWhere)
                .AsQuery().FillDataTable().Rows.Count > 0;
        }

        /// <summary>
        /// 查询实体集合，并排序
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="fields">查询字段</param>
        /// <param name="expr">查询条件Expression</param>
        /// <returns></returns>
        public List<T> Distinct(Expression<Func<T, object[]>> fields, Expression<Func<T, bool>> expr)
        {
            return EntityTable<T>.Instance
                .Select(fields)
                .Distinct()
                .Where(expr)
                .AsQuery().ToList<T>();
        }

        /// <summary>
        /// 查询实体集合，并排序
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="fields">查询字段</param>
        /// <param name="expr">查询条件Expression</param>
        /// <param name="orderFields">排序字段</param>
        /// <param name="order">排序方式</param>
        /// <returns></returns>
        public List<T> Distinct(Expression<Func<T, object[]>> fields, Expression<Func<T, bool>> expr, Expression<Func<T, object[]>> orderFields, OrderByType order)
        {
            return EntityTable<T>.Instance
                .Select(fields)
                .Distinct()
                .Where(expr)
                .OrderBy(orderFields, order)
                .AsQuery().ToList<T>();
        }

        /// <summary>
        /// 聚合函数方法
        /// </summary>
        /// <typeparam name="M"></typeparam>
        /// <param name="method">聚合方法名称</param>
        /// <param name="fields"></param>
        /// <param name="expr"></param>
        /// <returns></returns>
        public M Aggregate<M>(Aggregate method, Expression<Func<T, object[]>> fields, Expression<Func<T, bool>> expr)
        {
            EntityTable<T> entityTable = EntityTable<T>.Instance;
            List<string> paraList = EntityTableExtensions.ParseFieldList(entityTable, fields);
            entityTable.CommandText.Insert(0, string.Format("SELECT {0}({1}) FROM {2} ",
                method.ToString(),
                string.Join(", ", paraList.ToArray()),
                entityTable.GetTableName()));
            return entityTable.AsQuery().ExecuteScalar<M>();
        }

        #endregion
    }
}
